ALTER     PROC SP_REWORK_LOT_REPORT
  @PROCESSSTEP_ID INT,
  @FROM_DATE VARCHAR(20),
  @TO_DATE VARCHAR(20),
  @PROCESS_TYPE_NAME VARCHAR(20),
  @DEFAULT_PROMPT VARCHAR(20)
  
 AS
 BEGIN
 	DECLARE @PROCESSSTEPID BIGINT 
	DECLARE @TO_PROCESSSTEPID BIGINT 
	DECLARE @LOT_NUMBER VARCHAR(50) 
	DECLARE @FROM_PRODUCTNAME VARCHAR(50) 
	DECLARE @TO_PRODUCTNAME VARCHAR(50)
	DECLARE @REWORK_DATE DATETIME
	DECLARE @REWORK_QTY INT 
	DECLARE @FROM_PROCESS VARCHAR(30) 
	DECLARE @TO_PROCESS VARCHAR(30)
	DECLARE @FROM_LOCATION VARCHAR(30) 
	DECLARE @TO_LOCATION VARCHAR(30)
	DECLARE @PRODUCT_TYPE_ID INT
	DECLARE @PS_TYPE_Name VARCHAR(30)
	
	IF @PROCESS_TYPE_NAME='Mark and Pack'
	BEGIN
		SET @PROCESS_TYPE_NAME = 'MARK & PACK'
	END
	
	DECLARE @RESULTS TABLE( PROCESSSTEP_ID BIGINT,TO_PROCESSSTEP_ID BIGINT, LOT_NUMBER VARCHAR(50), FROM_PRODUCTNAME VARCHAR(50), TO_PRODUCTNAME VARCHAR(50), FROM_LOCATION VARCHAR(30), TO_LOCATION VARCHAR(30), REWORK_DATE VARCHAR(30), 
 				REWORK_QTY INT, FROM_PROCESS VARCHAR(30), TO_PROCESS VARCHAR(30),PRODUCT_TYPE_ID INT, PS_TYPE_Name VARCHAR(30))
	DECLARE @PARENT_CURSOR  CURSOR

	SET @PARENT_CURSOR = CURSOR FOR 
	SELECT 
	LT.PROCESSSTEP_ID,
	TO_PROCESSSTEP_ID,
	LT.LOT_NUMBER ,
	PS.PS_TYPE AS FROM_PROCESS, 
	(SELECT PS_TYPE FROM PROCESS_STEP WHERE PROCESSSTEP_ID = RLT.TO_PROCESSSTEP_ID ) AS TO_PROCESS,
	P.PART_NUMBER AS FROM_PART_NUMBER,
	(SELECT PART_NUMBER FROM PRODUCT WHERE PROCESSSTEP_ID = RLT.TO_PROCESSSTEP_ID ) AS TO_PART_NUMBER,
	(SELECT L_Vendor_Name FROM LOCATION WHERE LOCATION_ID = LT.LOCATION_ID) AS FROM_LOCATION,
	(SELECT L_Vendor_Name FROM LOCATION WHERE LOCATION_ID = RLT.TO_LOCATION_ID) AS TO_LOCATION, 
	RLT.REWORK_QTY, RLT.REWORK_DATE, 
	CASE 
		WHEN P_TYPE IS NULL  THEN 1
		WHEN P_Type ='BOARD' THEN 2
		WHEN P_TYPE ='KIT'THEN 3
		END AS PRODUCT_TYPE_ID,
	PS.PS_TYPE_Name AS PS_TYPE_Name 
	FROM REWORK_LOT_TABLE RLT, LOT_TABLE LT, PROCESS_STEP PS, PRODUCT P,LOCATION L
	WHERE RLT.LOT_ID = LT.LOT_ID AND LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
	AND PS.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND LT.LOCATION_ID = L.LOCATION_ID
	
	OPEN @PARENT_CURSOR 
	FETCH NEXT FROM @PARENT_CURSOR INTO @PROCESSSTEPID, @TO_PROCESSSTEPID, @LOT_NUMBER, @FROM_PROCESS, @TO_PROCESS, @FROM_PRODUCTNAME, @TO_PRODUCTNAME, @FROM_LOCATION, @TO_LOCATION, @REWORK_QTY, @REWORK_DATE,@PRODUCT_TYPE_ID,@PS_TYPE_Name
	WHILE @@FETCH_STATUS = 0
	BEGIN
			
		IF @PS_TYPE_Name='MARK & PACK'
		BEGIN
			SET @PS_TYPE_Name = 'Mark and Pack'
		END
			
		INSERT INTO @RESULTS ( PROCESSSTEP_ID, TO_PROCESSSTEP_ID, LOT_NUMBER, FROM_PRODUCTNAME, TO_PRODUCTNAME, FROM_LOCATION, TO_LOCATION, REWORK_DATE, REWORK_QTY, FROM_PROCESS, TO_PROCESS, PRODUCT_TYPE_ID,PS_TYPE_Name)
		VALUES (@PROCESSSTEPID, @TO_PROCESSSTEPID, @LOT_NUMBER, @FROM_PRODUCTNAME, @TO_PRODUCTNAME, @FROM_LOCATION, @TO_LOCATION, CONVERT(VARCHAR,CONVERT(DATETIME,@REWORK_DATE),101) , @REWORK_QTY, @FROM_PROCESS, @TO_PROCESS, @PRODUCT_TYPE_ID,@PS_TYPE_Name)
		
	FETCH NEXT FROM	@PARENT_CURSOR INTO @PROCESSSTEPID, @TO_PROCESSSTEPID, @LOT_NUMBER, @FROM_PROCESS, @TO_PROCESS, @FROM_PRODUCTNAME, @TO_PRODUCTNAME, @FROM_LOCATION, @TO_LOCATION, @REWORK_QTY, @REWORK_DATE,@PRODUCT_TYPE_ID,@PS_TYPE_Name

	END
	
	CLOSE @PARENT_CURSOR
	DEALLOCATE @PARENT_CURSOR	

 	IF (@PROCESSSTEP_ID = 0) AND (@FROM_DATE = '0') AND (@TO_DATE = '0') 
	BEGIN
	SELECT * FROM @RESULTS
	END
	ELSE IF (@PROCESSSTEP_ID = 0) AND (@FROM_DATE <> '0') AND (@TO_DATE <> '0')
 	BEGIN
	SELECT * FROM @RESULTS WHERE  REWORK_DATE <= CONVERT(DATETIME,@TO_DATE) AND REWORK_DATE >= CONVERT(DATETIME,@FROM_DATE)
	END
	ELSE IF (@PROCESSSTEP_ID = 0) AND (@FROM_DATE = '0') AND (@TO_DATE <> '0')
 	BEGIN
	SELECT * FROM @RESULTS WHERE  REWORK_DATE <= CONVERT(DATETIME,@TO_DATE) 
	END
	ELSE IF (@PROCESSSTEP_ID = 0) AND (@FROM_DATE <> '0') AND (@TO_DATE = '0')
 	BEGIN
	SELECT * FROM @RESULTS WHERE  REWORK_DATE >= CONVERT(DATETIME,@FROM_DATE)
	END

	ELSE IF (@PROCESSSTEP_ID <> 0) AND (@FROM_DATE = '0') AND (@TO_DATE = '0') 
	BEGIN
	SELECT * FROM @RESULTS WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID
	END
	ELSE IF (@PROCESSSTEP_ID <> 0) AND (@FROM_DATE <> '0') AND (@TO_DATE <> '0')
 	BEGIN
	SELECT * FROM @RESULTS WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND REWORK_DATE <= CONVERT(DATETIME,@TO_DATE) AND REWORK_DATE >= CONVERT(DATETIME,@FROM_DATE)
	END
	ELSE IF (@PROCESSSTEP_ID <> 0) AND (@FROM_DATE = '0') AND (@TO_DATE <> '0')
 	BEGIN
	SELECT * FROM @RESULTS WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND REWORK_DATE <= CONVERT(DATETIME,@TO_DATE) 
	END
	ELSE IF (@PROCESSSTEP_ID <> 0) AND (@FROM_DATE <> '0') AND (@TO_DATE = '0')
 	BEGIN
	SELECT * FROM @RESULTS WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND REWORK_DATE >= CONVERT(DATETIME,@FROM_DATE)
	END
 END